package com.kimstore.dao.impl;

import java.util.List;

import javax.persistence.Query;

import com.kimstore.bean.Product;
import com.kimstore.dao.ProductDao;
import com.kimstore.utilities.QueryElements;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;

public class ProductDaoImpl extends GenericJpaDaoImpl<Product, Integer> implements ProductDao {

    @Override
    @SuppressWarnings("unchecked")
    public List<Product> getStocks() {
        Query query = getEntityManager().createQuery("SELECT NEW com.kimstore.bean.Product(id, name, (SELECT count(item) FROM Item item where item.product = product and item.purchaseDetail is empty), specifications, status) FROM Product product");
        List<Product> products = query.getResultList();

        return products;
    }

    @Override
    public Product findByName(String name) {
        Query query = getEntityManager().createQuery("select p from Product p where p.name = :name").setParameter("name", name);
        Product product = (Product) getSingleResultOrNull(query);

        return product;
    }

    @Override
    public boolean existsName(String name) {
        Query query = getEntityManager().createQuery("select count(product) from Product product where product.name = :name");
        query.setParameter("name", name);
        long count = (long) query.getSingleResult();

        return count > 0;
    }

    @Override
    public List<Product> findAllActive() {
        Query query = getEntityManager().createQuery("from Product product where product.status = :status");
        query.setParameter("status", Product.ACTIVE);
        List<Product> products = query.getResultList();

        return products;

    }



    @SuppressWarnings("unchecked")
    private List<Product> getSellingProducts() {
        Query query = getEntityManager().createQuery("Select product from Product product, Item item where item.product = product and item.purchaseDetail is not null and item.purchaseDetail.dateSold between :startDate and :endDate group by product");

        Calendar calendar = Calendar.getInstance();

        calendar.set(Calendar.HOUR_OF_DAY, 23);
        calendar.set(Calendar.MINUTE, 59);
        calendar.set(Calendar.SECOND, 59);
        System.out.print("FROM " + calendar.getTime() + " ");
        query.setParameter("endDate", calendar.getTime());

        calendar.add(Calendar.DAY_OF_MONTH, -7);
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        System.out.println("TO " + calendar.getTime() + " ");
        query.setParameter("startDate", calendar.getTime());

        List<Product> sellingProducts = query.getResultList();

        return sellingProducts;
    }
    
        @Override
    public List<Product> getBestSellingProducts() {
        Query query = getEntityManager().createQuery("Select NEW com.kimstore.bean.Product(product.id, product.name, count(product), product.specifications, product.status) from Product product, Item item where item.purchaseDetail is not null and item.product = product group by product order by count(product) DESC");
        List<Product> products = query.getResultList();

        return products;
    }

    @Override
    public List<Product> getUnderperformingProducts() {
        List<Product> sellingProducts = this.getSellingProducts();
        List<Product> underperformingProducts = this.findAll();
        underperformingProducts.removeAll(sellingProducts);

        return underperformingProducts;
    }
    
        @Override
    public List<Product> getBestSellingProducts(Date startDate, Date endDate) {
        Query query = getEntityManager().createQuery("Select NEW com.kimstore.bean.Product(product.id, product.name, count(product), product.specifications, product.status) from Product product, Item item where item.purchaseDetail is not null and item.product = product and item.purchaseDetail.dateSold between :startDate and :endDate group by product order by count(product) DESC");
        List<Product> products = query.getResultList();
        query.setParameter("startDate", startDate);
        query.setParameter("endDate", endDate);
        
        return products;    }


    @Override
    public List<Product> getGrossSales(Date startDate, Date endDate) {
        Query query = getEntityManager().createQuery("Select new com.kimstore.bean.Product(product.name, count(item), sum(item.purchaseDetail.priceSold)) from Product product, Item item where item.product = product and item.purchaseDetail is not null and item.purchaseDetail.dateSold between :startDate and :endDate group by product order by product.name");
        query.setParameter("startDate", startDate);
        query.setParameter("endDate", endDate);

        List<Product> products = query.getResultList();

        for (Product product1 : this.findAll()) {
            boolean found = false;

            for (Product product2 : products) {
                if (product1.getName().equals(product2.getName())) {
                    found = true;
                    break;
                }
            }

            if (!found) {
                products.add(product1);
            }
        }

        return products;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Product> getNetIncome(Date startDate, Date endDate) {
        Query query = getEntityManager().createQuery("Select new com.kimstore.bean.Product(product.name, count(item), sum(item.purchaseDetail.priceSold)) from Product product, Item item where item.product = product and item.purchaseDetail is not null and item.purchaseDetail.dateSold between :startDate and :endDate group by product order by product.name");
        query.setParameter("startDate", startDate);
        query.setParameter("endDate", endDate);

        List<Product> products = query.getResultList();

        for (Product product1 : this.findAll()) {
            boolean found = false;

            for (Product product2 : products) {
                if (product1.getName().equals(product2.getName())) {
                    found = true;
                    break;
                }
            }

            if (!found) {
                products.add(product1);
            }
        }

        return products;
    }

    @Override
    public List<Product> findAll(int startIndex, int maxNumber) {
        Query query = getEntityManager().createQuery("from Product product order by product.name");
        query.setFirstResult(startIndex);
        query.setMaxResults(maxNumber);

        List<Product> products = query.getResultList();

        return products;
    }

    @Override
    public long count() {
        Query query = getEntityManager().createQuery("select count(product) from Product product");

        long count = (long) query.getSingleResult();

        return count;
    }

    @Override
    public List<Product> filter(QueryElements queryElements, long startIndex, long numberOfItems) {
        System.out.println("QUERY: " + queryElements.getQuery());
        Query query = getEntityManager().createQuery("SELECT NEW com.kimstore.bean.Product(id, name, (SELECT count(item) FROM Item item where item.product = product and item.purchaseDetail is empty) as availableStocks, specifications, status, basePrice) " + queryElements.getQuery().replace("product.availableStocks", "availableStocks"));
        
        //System.out.println("1");

        if (queryElements.getParameters() != null) {
            Iterator it = queryElements.getParameters().entrySet().iterator();
            //System.out.println(queryElements.getParameters());
            while (it.hasNext()) {
                Map.Entry e = (Map.Entry) it.next();
                query.setParameter(e.getKey().toString(), e.getValue());
            }
        }
        //System.out.println("2");
        query.setFirstResult(Integer.parseInt(startIndex + ""));
        query.setMaxResults(Integer.parseInt(numberOfItems + ""));
        //System.out.println("3");
        List<Product> results = query.getResultList();
        
        return results;
    }

    @Override
    public long count(QueryElements queryElements) {
        System.out.println("QUERY IN DAO IMPL: " + queryElements.getQuery());
        Query query = getEntityManager().createQuery("SELECT NEW com.kimstore.bean.Product(id, name, (SELECT count(item) FROM Item item where item.product = product and item.purchaseDetail is empty) as availableStocks, specifications, status) " + queryElements.getQuery().replace("product.availableStocks", "availableStocks"));

        if (queryElements.getParameters() != null) {
            Iterator it = queryElements.getParameters().entrySet().iterator();
            //System.out.println(queryElements.getParameters());
            while (it.hasNext()) {
                Map.Entry e = (Map.Entry) it.next();
                query.setParameter(e.getKey().toString(), e.getValue());
            }
        }
        long count = (long) query.getResultList().size();
                
        return count;
    }

}
